package com.haochu.novel.db;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.haochu.novel.vo.Book;
import com.haochu.novel.vo.Category;

/**
 * @author fuhuichao
 * 2017年6月23日10:40:37
 * 数据库工具类
 */
public class DBHelper extends SQLiteOpenHelper {

	// 数据库版本
	private static final int DATABASE_VERSION = 1;
	// 数据库名
	private static final String DATABASE_NAME = "haochubook.db";
	// 小说表
	private static final String TABLE_NAME_BOOK = "book";
	// 目录表
	private static final String TABLE_NAME_CATEGORY = "category";
	// 删除目录SQL
	private static final String SQL_DELETE_CATEGORY = "delete from " + TABLE_NAME_CATEGORY + " where book_id = ?";
	// 批量保存目录
	private static final String SQL_INSERT_CATEGORY = "insert into " + TABLE_NAME_CATEGORY + "(book_id, name, position) values (?, ?, ?)";
	
	
	public DBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	/**
	 * 初始化创建表
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		
		String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME_BOOK + " (" +
				"id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
				"path TEXT NOT NULL, " +
				"name TEXT NOT NULL);";
		db.execSQL(sql);
		
		sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME_CATEGORY+" (" +
				"id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
				"book_id INTEGER NOT NULL, " +
				"name TEXT NOT NULL, " +
				"position INTEGER NOT NULL);";
		db.execSQL(sql);
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("drop table if exists " + TABLE_NAME_CATEGORY);
		db.execSQL("drop table if exists " + TABLE_NAME_BOOK);
		onCreate(db);
	}
	
	/**
	 * 保存一本书
	 */
	public void addBook(String path, String name){
		String sql = "insert into " + TABLE_NAME_BOOK + " (path, name) values (?, ?);";
		
		// 先验证该路径的书是否已存在
		Book book = getBookByPath(path);
		if(book == null){
			SQLiteDatabase db = getWritableDatabase();
			db.execSQL(sql, new Object[]{path, name});
			db.close();
		}
	}
	
	/**
	 * 根据点击的id获取一本书
	 */
	public Book getBookById(int bookId){
		Book book = null;
		String sql = "select * from " + TABLE_NAME_BOOK + " where id = ?";

		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, new String[]{bookId+""});
		while (cursor.moveToNext()) {
			book = new Book();
			book.setId(cursor.getInt(0));
			book.setPath(cursor.getString(1));
			book.setName(cursor.getString(2));
		}
		db.close();
		
		return book;
	}
	
	/**
	 * 根据路径获取一本书
	 */
	public Book getBookByPath(String path){
		Book book = null;
		String sql = "select * from " + TABLE_NAME_BOOK + " where path = ?";
		
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, new String[]{path});
		while (cursor.moveToNext()) {
			book = new Book();
			book.setId(cursor.getInt(0));
			book.setPath(cursor.getString(1));
			book.setName(cursor.getString(2));
		}
		db.close();
		
		return book;
	}
	
	/**
	 * 保存章节目录
	 */
	public void addCategories(int bookId, List<Category> categories){
		SQLiteDatabase db = getWritableDatabase();
		
		// 开始事务
		db.beginTransaction();
		
		try {
			// 先删除目录
			db.execSQL(SQL_DELETE_CATEGORY, new Object[]{bookId});
			
			// 批量插入目录
			for(Category category : categories){
				db.execSQL(SQL_INSERT_CATEGORY, new Object[]{bookId, category.getName(), category.getPosition()});
			}
			
			// 设置事务处理成功，不设置会自动回滚不提交
			db.setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 提交事务
			db.endTransaction();
			db.close();
		}
	}
	
	/**
	 * 获取章节目录
	 */
	public List<Category> getCategories(int bookId){
		List<Category> categories = new ArrayList<Category>();
		Category category = null;
		
		String sql = "select * from " + TABLE_NAME_CATEGORY + " where book_id = ?";
		
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, new String[]{bookId+""});
		while (cursor.moveToNext()) {
			category = new Category();
			category.setId(cursor.getInt(0));
			category.setBookId(cursor.getInt(1));
			category.setName(cursor.getString(2));
			category.setPosition(cursor.getInt(3));
			categories.add(category);
		}
		db.close();
		
		return categories;
	}
	
	/**
	 * 获取页面当前位置的标题
	 */
	public String findCategoryByPosition(int position){
		String category = "";
		String sql = "select name from " + TABLE_NAME_CATEGORY + " where position <= ? order by position desc limit 1";
		
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = db.rawQuery(sql, new String[]{position + ""});
		while(c.moveToNext()){
			category = c.getString(0);
		}
		return category;
	}
}
